package dao;

import modle.AthleteCompetitionInformation;
import modle.JDBCUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class AthleteCompetitionInformationDao {
    private Connection connection = null;
    public List<AthleteCompetitionInformation> getSearchAthleteCompetitionInformation(int ci_id,String ano, String status) {
        List<AthleteCompetitionInformation> list = new ArrayList<AthleteCompetitionInformation>();
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        StringBuilder sql = new StringBuilder("CALL C_P_Search_AthleteCompetitionInformation(?)");//参数占位符
        ResultSet rs = null;
        try{
            connection = JDBCUtil.getConn();
            pstmt = connection.prepareStatement(sql.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            pstmt.setInt(1, ci_id);
            rs = pstmt.executeQuery();
            int num = 1;//项目序号
            while (rs.next()) {
                Calendar cal = Calendar.getInstance();
                // 获取时间戳信息
                Timestamp rtimestamp = rs.getTimestamp("PIorFI_RecordingTime");
                Timestamp rnewTimestamp = null;
                if(rtimestamp != null){
                    cal.setTimeInMillis(rtimestamp.getTime());
                    // 对Calendar对象进行处理，减少8个小时
                    cal.add(Calendar.HOUR_OF_DAY, -8);

                    // 使用Calendar对象创建一个新的timestamp对象
                    rnewTimestamp = new java.sql.Timestamp(cal.getTime().getTime());
                }
                AthleteCompetitionInformation athleteCompetitionInformation = new AthleteCompetitionInformation();
                athleteCompetitionInformation.setNum(num++);
                athleteCompetitionInformation.setId(rs.getInt("PIorFI_ID"));
                athleteCompetitionInformation.setA_id(rs.getInt("A_ID"));
                athleteCompetitionInformation.setA_name(rs.getString("A_Name"));
                athleteCompetitionInformation.setA_no(rs.getString("A_No"));
                athleteCompetitionInformation.setA_gender(rs.getString("A_Gender"));
                athleteCompetitionInformation.setA_college_id(rs.getInt("A_College_ID"));
                athleteCompetitionInformation.setA_college_name(rs.getString("A_College_Name"));
                athleteCompetitionInformation.setA_tel(rs.getString("A_Tel"));
                athleteCompetitionInformation.setA_group_id(rs.getInt("A_Group_ID"));
                athleteCompetitionInformation.setA_group_name(rs.getString("A_Group_Name"));
                athleteCompetitionInformation.setR_id(rs.getInt("R_ID"));
                athleteCompetitionInformation.setI_id(rs.getInt("I_ID"));
                athleteCompetitionInformation.setRecordingStatus(rs.getInt("PIorFI_RecordingStatus"));
                athleteCompetitionInformation.setRecordingTime(rnewTimestamp);
                athleteCompetitionInformation.setGroup(rs.getInt("PIorFI_Group"));
                athleteCompetitionInformation.setRunway(rs.getInt("PIorFI_Runway"));
                athleteCompetitionInformation.setGrade(rs.getString("PIorFI_Grade"));
                if(ano!= null && !ano.equals("") && !ano.equals(athleteCompetitionInformation.getA_no()))
                    continue;
                if(status!= null && !status.equals("")){
                    if(Integer.parseInt(status) == 1 && athleteCompetitionInformation.getGrade() == null)
                        continue;
                    if(Integer.parseInt(status) == 2 && athleteCompetitionInformation.getGrade() != null)
                        continue;
                    if(Integer.parseInt(status) == 3 && athleteCompetitionInformation.getRecordingStatus().equals("已检录"))
                        continue;
                }
                list.add(athleteCompetitionInformation);
            }
            pstmt.close();
            connection.close();
        }catch (SQLException e) {
            //e.printStackTrace();
        }
        catch (NullPointerException e){
            return null;
        }finally{
            try{
                if (connection != null && (!connection.isClosed())){
                    connection.close();
                }
            }catch(SQLException e){
                //e.printStackTrace();
            }
        }
        return list;
    }
    public void upDateAthleteCompetitionScore(int ci_id,int id,String score,int r_id){
        PreparedStatement pstmt = null;//声明一个PreparedStatement对象并将其初始化为null
        String sql = "CALL C_P_UpdateAthleteGrade(?,?,?,?);";//参数占位符
        ResultSet rs = null;
        JDBCUtil db = new JDBCUtil();
        try {
            Connection connection = db.getConn();
            pstmt = connection.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            //ResultSet.TYPE_SCROLL_INSENSITIVE：游标可以前后移动，结果集对创建结果集后其他人对数据库所做的更改不敏感
            // 结果集是只读的，这是默认的并发类型
            pstmt.setInt(1, ci_id);
            pstmt.setInt(2, id);
            pstmt.setString(3, score);
            pstmt.setInt(4,r_id);
            pstmt.execute();
            pstmt.close();
            connection.close();

        }catch (SQLException e) {

        }finally {

        }
    }
}
